package com.orm;

import java.sql.*;

/**
 * MySql数据库操作类 单例模式，实例化请使用getInstance方法
 */
public class Db implements AutoCloseable {

    private static Db _db = new Db();
    private Connection _connection;

    private Db() {
        try {
            Class.forName(DbConfiguration.DriverName);     // 动态加载数据库驱动
            _connection = DriverManager.getConnection(DbConfiguration.ConnectionString);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取已连接的数据库连接对象
     *
     * @return 已连接的数据库连接对象
     * @throws SQLException
     */
    public Connection getOpenedConnection() throws SQLException {
        if (_connection == null || _connection.isClosed()) {
            try {
                _connection = DriverManager.getConnection(DbConfiguration.ConnectionString);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return _connection;
    }

    /**
     * 获取单例模式的实例
     *
     * @return Db操作实例
     */
    public static Db getInstance() {
        return _db;
    }

    /**
     * 自动释放资源
     */
    @Override
    public void close() {
        try {
            if (_connection != null && !_connection.isClosed()) {
                _connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据指定的查询语句，获取相对应的ResultSet
     * <B>需要手动关闭ResultSet</B>，否则会造成资源浪费
     *
     * @param sql 需要执行的查询语句
     * @return <B>需要手动关闭ResultSet</B>
     * @throws SQLException
     */
    public ResultSet executeQuery(String sql) throws SQLException {
        Connection connection = getOpenedConnection();
        Statement statement = connection.createStatement();
//        statement.closeOnCompletion();
        return statement.executeQuery(sql);
    }

    /**
     * Executes the SQL query and returns the ResultSet object generated by the
     * query.
     *
     * @param sql 可以含'?'的sql语句
     * @param args 替换'?'参数
     * @return a ResultSet object that contains the data produced by the query;
     * never null
     * @throws SQLException
     */
    public ResultSet executeQuery(String sql, Object... args) throws SQLException {
        Connection connection = getOpenedConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.closeOnCompletion();
        for (int i = 1; i <= args.length; ++i) {
            preparedStatement.setObject(i, args[i - 1]);
        }

        try {
            return preparedStatement.executeQuery();

        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 根据指定的sql语句，返回第一行第一列的结果
     *
     * @param sql 需要执行的sql语句
     * @return 第一行第一列的结果
     */
    public Object executeScaler(String sql) throws SQLException {
        ResultSet rs = executeQuery(sql);
        Object value = null;
        if (rs.next()) {
            value = rs.getObject(1);
        }
        return value;
    }

    /**
     * Executes the given SQL statement, which may be an INSERT, UPDATE, or
     * DELETE statement or an SQL statement that returns nothing, such as an SQL
     * DDL statement.
     *
     * @param sql 需要执行的sql语句
     * @return 受影响的结果条数
     * @throws SQLException
     */
    public int executeUpdate(String sql) throws SQLException {
        Connection connection = getOpenedConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            return statement.executeUpdate(sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    /**
     * Executes the SQL statement, which must be an SQL Data Manipulation
     * Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL
     * statement that returns nothing, such as a DDL statement.
     *
     * @param sql 可以含'?'的sql语句
     * @param args 替换'?'参数
     * @return either (1) the row count for SQL Data Manipulation Language (DML)
     * statements or (2) 0 for SQL statements that return nothing
     * @throws SQLException
     */
    public int executeUpdate(String sql, Object... args) throws SQLException {
        Connection connection = getOpenedConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= args.length; ++i) {
            preparedStatement.setObject(i, args[i - 1]);
        }
        try {
            return preparedStatement.executeUpdate();
        } finally {
            preparedStatement.close();
        }
    }

    /**
     * 执行指定的查询语句，如果为查询语句，返回ResultSet，其余的返回受影响的条数
     *
     * @param sql 需要执行的sql语句
     * @return 如果为查询语句，返回ResultSet，其余的返回受影响的条数
     * @throws SQLException
     */
    public Object execute(String sql) throws SQLException {
        Connection connection = getOpenedConnection();
        Object result = null;
        Statement statement = null;
        statement = connection.createStatement();
        if (statement.execute(sql)) {
            result = statement.getResultSet();
            statement.closeOnCompletion();
        } else {
            result = statement.getUpdateCount();
            statement.close();
        }
        return result;
    }

    /**
     * Executes the SQL statement
     *
     * @param sql 可以含'?'的sql语句
     * @param args 替换'?'参数
     * @return 如果为查询语句，返回ResultSet，否则返回受影响的行数
     * @throws SQLException
     */
    public Object execute(String sql, Object... args) throws SQLException {
        Connection connection = getOpenedConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        Object result;
        for (int i = 1; i <= args.length; ++i) {
            preparedStatement.setObject(i, args[i - 1]);
        }
        if (preparedStatement.execute()) {
            result = preparedStatement.getResultSet();
            preparedStatement.closeOnCompletion();
        } else {
            result = preparedStatement.getUpdateCount();
            preparedStatement.close();
        }
        return result;
    }


}

